package nhan.busMap.sqlLite;

/**
 * 
 * @author TRAN VAN NHAN
 * @date  3/15/2012
 * 
 */

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class playWithsqlLite {

	/**
	 * @param args
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
		Workbook workbook = null;
		try {
			workbook = Workbook.getWorkbook(new File("Bus01.xls"));   // save file excel vao trong workbook
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		Sheet sheet = workbook.getSheet(0);  // lay ra sheet can xu ly, tinh bat dau tu 0
		//Cell a1 = sheet.getCell(2, 7);   // lay ra cot hang 4 cot 7, tinh bat dau tu 0
		//Cell a2 = sheet.getCell(0,7);
		
		//String stringa1 = a1.getContents();  // lay noi dung cua cot luu vao bien stringa1
		//String stringa2 = a2.getContents();
		//System.out.println("stringa1: "+stringa1);
		//System.out.println("stringa2: "+stringa2);
		
		  // close work de free memory
		//----------------------------------------------------------------------------------------
		Class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:dulieu.db");  // tao ket noi den co so du lieu test.db, neu chua co thi tao co so du lieu test.db trong cung thu muc voi project
        Statement stat = conn.createStatement();
        //stat.executeUpdate("drop table if exists table1;");  // thuc hien cau lenh tao bang
        //stat.executeUpdate("create table table1 (STT, MST, MSB);");
        for(int i=19;i<=26;i++){
        	Cell a1 = sheet.getCell(4, i);   // lay ra cot hang 4 cot 7, tinh bat dau tu 0
    		//Cell a2 = sheet.getCell(0,7);
    		
    		String stringa1 = a1.getContents();  // lay noi dung cua cot luu vao bien stringa1
    		
    		//String stringa2 = a2.getContents();
    		String smt="insert into table1 values ( (SELECT MAX(STT) + 1 FROM table1)," +stringa1+",(SELECT (MAX(STT)+1)||\"_\"||"+stringa1+" FROM table1))";
        	stat.executeUpdate(smt);
        }
        workbook.close(); 

        conn.setAutoCommit(false);  // khong tu dong luu data prepare vao table, nham muc dich cai thien toc do xu ly
        //prep.executeBatch();   // tien hanh them du lieu vao table, luc nay du lieu moi chinh thuc duoc luu vao table
        conn.setAutoCommit(true);  // tra ve gia tri true cho comit

//        ResultSet rs = stat.executeQuery("select * from table1;");  // rs la con tro o bang tra ve
//        while (rs.next()) {
//            System.out.println("name = " + rs.getString("STT"));
//            System.out.println("job = " + rs.getString("MST"));
//        }
//        rs.close();
        conn.close();
	}

}